
# import libraries

import pandas as pd
import plotly.express as px
from datetime import datetime, timedelta
from matplotlib.pyplot import figure
import seaborn as sns
sns.set(rc={'figure.figsize':(10, 8)})
from matplotlib import pyplot as plt
import numpy as np
from scipy import stats as st
%matplotlib inline
import warnings
warnings.filterwarnings('ignore')


#function for primary analysis of the dataset

def dataset (dataset):
    dataset.columns = [x.lower().replace(' ', '_') for x in dataset.columns.values]
    display(dataset.info())
    print('*'*50)
    display(dataset.describe())
    print('*'*50)
    display(dataset.sample(10))
    print('*'*50)
    display('Number of duplicate values', dataset.duplicated().sum())


visits = pd.read_csv('/datasets/game_actions.csv')
costs = pd.read_csv('/datasets/ad_costs.csv')
sources = pd.read_csv('/datasets/user_source.csv')

dataset(visits)

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 135640 entries, 0 to 135639
Data columns (total 5 columns):
 #   Column          Non-Null Count   Dtype 
---  ------          --------------   ----- 
 0   event_datetime  135640 non-null  object
 1   event           135640 non-null  object
 2   building_type   127957 non-null  object
 3   user_id         135640 non-null  object
 4   project_type    1866 non-null    object
dtypes: object(5)
memory usage: 5.2+ MB

None

**************************************************

**************************************************

**************************************************

'Number of duplicate values'

1


visits = visits.drop_duplicates()
visits['event_datetime'] = pd.to_datetime(visits['event_datetime']) # we bring the data to the datetime format


visits['project_type'].unique()

array([nan, 'satellite_orbital_assembly'], dtype=object)


visits.query('project_type=="satellite_orbital_assembly"').count()

event_datetime    1866
event             1866
building_type        0
user_id           1866
project_type      1866
dtype: int64


visits.query('event=="project"').count()

event_datetime    1866
event             1866
building_type        0
user_id           1866
project_type      1866
dtype: int64


dataset(costs)

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 28 entries, 0 to 27
Data columns (total 3 columns):
 #   Column  Non-Null Count  Dtype  
---  ------  --------------  -----  
 0   source  28 non-null     object 
 1   day     28 non-null     object 
 2   cost    28 non-null     float64
dtypes: float64(1), object(2)
memory usage: 800.0+ bytes

None

**************************************************

**************************************************

**************************************************

'Number of duplicate values'

0


costs['day'] = pd.to_datetime(costs['day']) # we bring the data to the datetime format


costs['source'].unique()

array(['facebook_ads', 'instagram_new_adverts', 'yandex_direct',
       'youtube_channel_reklama'], dtype=object)


display(costs['day'].max(), costs['day'].min())

Timestamp('2020-05-09 00:00:00')

Timestamp('2020-05-03 00:00:00')


dataset(sources)

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 13576 entries, 0 to 13575
Data columns (total 2 columns):
 #   Column   Non-Null Count  Dtype 
---  ------   --------------  ----- 
 0   user_id  13576 non-null  object
 1   source   13576 non-null  object
dtypes: object(2)
memory usage: 212.2+ KB

None

**************************************************

**************************************************

**************************************************

'Number of duplicate values'

0


players_number = sources['user_id'].nunique()
players_number

13576


events = len(visits['user_id'])
users = len(visits['user_id'].unique())
average_events_per_user = events / users

display('Total number of events', events)
display('Total number of users', users)
display('Average number of events per user', '{:.2f}'.format(average_events_per_user))

'Total number of events'

135639

'Total number of users'

13576

'Average number of events per user'

'9.99'


visits['event_datetime'].hist(figsize = (13,5), bins=50)
plt.title('Histogram by date and time')
plt.ylabel('Number of observations')
plt.show()


visits_finished_stage_1 = visits.query('event=="finished_stage_1"')
visits_finished_stage_1['event_datetime'].hist(figsize = (15,7), bins=50)
plt.title('Histogram of level completion events by date and time')
plt.ylabel('Number of observations')
plt.show()


users

13576


visits['day_week'] = pd.DatetimeIndex(visits['event_datetime']).day_name()
temp = visits.groupby('day_week')['user_id'].nunique().reset_index()
temp['share'] = temp['user_id']/len(visits['user_id'].unique())*100

week = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
temp['day_week'] = pd.Categorical(temp['day_week'], categories=week, ordered=True)
temp = temp.sort_values('day_week')

fig = px.bar(temp, x='day_week', y='share')

fig.update_layout(barmode='stack',  
                  title_text='Distribution of active users by day of the week, %',
                  yaxis=dict(
                      title='% from all users', 
                      titlefont_size=15,
                      tickfont_size=10,
                  ), width=700, height=400)
fig.show()


visits_building_type = visits.groupby('building_type')['user_id'].count().reset_index()

fig = px.bar(visits_building_type, x='building_type', y='user_id')

fig.update_layout(barmode='stack', 
                  xaxis={'categoryorder':'total descending'}, 
                  title_text='Number of events for the selected building type',
                  yaxis=dict(
                      title='Number of buildings', 
                      titlefont_size=15,
                      tickfont_size=10,
                  ), width=700, height=400)
fig.show()


visits_event = visits.groupby('event')['user_id'].count().reset_index()

fig = px.bar(visits_event, x='event', y='user_id')

fig.update_layout(barmode='stack', 
                  xaxis={'categoryorder':'total descending'}, 
                  title_text='Distribution of observations by event type',
                  yaxis=dict(
                      title='Number of players', 
                      titlefont_size=15,
                      tickfont_size=10,
                  ), width=700, height=400)
fig.show()


# record in a separate dataframe all the events about the players who completed the first level
completed = visits.query('event == "finished_stage_1"')
display('The number of players who completed the first level', len(completed))

'The number of players who completed the first level'

5817


share = len(completed['user_id']) / players_number
display('The percentage of players who completed the first level, total', '{:.2%}'.format(share))

'The percentage of players who completed the first level, total'

'42.85%'


share_project = len(visits.query('project_type == "satellite_orbital_assembly"'))/ players_number
display('Percentage of players who completed the first level through the implementation of the project (from all players)', 
        '{:.2%}'.format(share_project))

'Percentage of players who completed the first level through the implementation of the project (from all players)'

'13.74%'


share_victory = share - share_project
display('Percentage of players who completed the first level by defeating another player (from all players)', 
        '{:.2%}'.format(share_victory))

'Percentage of players who completed the first level by defeating another player (from all players)'

'29.10%'


#save the IDs of the users who completed the project
project = visits.query('event == "project"')

users_project = project['user_id'].tolist() 

#function for categorizing the method of passing the first level by the user
def define_completion_type (id):
    if id in users_project:
        return "project"
    else:
        return "victory" 

#adding a parameter  
completed['type_of_completion'] = completed.apply(
    lambda x: define_completion_type(x.user_id
                                    ), axis=1
)


#let's count the number of "building" events for each user
building_count = visits.query(
    'event == "building"').groupby('user_id').agg({'event':'count'}).reset_index().rename(
    columns={'event':'building_count'}
)

building_count = pd.merge(building_count, completed, how="inner", on='user_id')

building_count.head(5)


#let's count the number of users who have moved to the next level without having a single building in the logs
temp = building_count.query('event=="finished_stage_1" & building_count==0')
print(len(temp))

0


#save in a separate table the users who completed the level by building the object
visits_project = visits.query('event=="project"')
visits_project = visits_project['user_id'].to_frame()

#save information with user IDs and building type in a separate table
visits_building = visits.query('event=="building"')
visits_building = visits_building[['building_type', 'user_id']]
visits_building = visits_building.drop_duplicates()
visits_building


#supplement the table about the players who completed the level with the selected type of building 
visits_building_nextlevel = pd.merge(visits_project, visits_building, how="inner", on='user_id')
visits_building_nextlevel = visits_building_nextlevel.drop_duplicates()
visits_building_nextlevel.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 5598 entries, 0 to 5597
Data columns (total 2 columns):
 #   Column         Non-Null Count  Dtype 
---  ------         --------------  ----- 
 0   user_id        5598 non-null   object
 1   building_type  5598 non-null   object
dtypes: object(2)
memory usage: 131.2+ KB


visits_building_nextlevel = visits_building_nextlevel.groupby('building_type')['user_id'].count().reset_index()

fig = px.bar(visits_building_nextlevel, x='building_type', y='user_id')

fig.update_layout(barmode='stack', 
                  xaxis={'categoryorder':'total descending'}, 
                  title_text='Distribution of players who have completed the project by task type',
                  yaxis=dict(
                      title='Number of buildings', 
                      titlefont_size=15,
                      tickfont_size=10,
                  ), width=700, height=400)
fig.show()


#calculate the time of the first event for each user
time = visits.groupby(by=["user_id"])['event_datetime'].min().reset_index()
time.rename(columns = {'event_datetime':'first_ts'}, inplace = True)

#calculate the time of the last event for each user
time_last = visits.groupby(by=["user_id"])['event_datetime'].max().reset_index()
time_last.rename(columns = {'event_datetime':'last_ts'}, inplace = True)

#let's combine it into one table by user ID
time = pd.merge(time, time_last, how="inner", on='user_id')
time['usage_duration'] = time['last_ts'] - time['first_ts']

display('Average time between events for 1 user', time['usage_duration'].mean())
display('Median time between events for 1 user', time['usage_duration'].median())
display('Maximum time between events for 1 user', time['usage_duration'].max())
display('Minimum time between events for 1 user', time['usage_duration'].min())

'Average time between events for 1 user'

Timedelta('10 days 16:13:07.579183853')

'Median time between events for 1 user'

Timedelta('10 days 12:30:52.500000')

'Maximum time between events for 1 user'

Timedelta('30 days 21:58:53')

'Minimum time between events for 1 user'

Timedelta('0 days 00:00:00')


display(visits.sort_values(by='user_id',ascending=False).head(10))


#let's take the first user as an example
visits.query('user_id=="ffff69cc-fec1-4fd3-9f98-93be1112a6b8"')


#let's add data about the time spent in the game, the first and last event to the dataframe
completed = pd.merge(completed, time, how="inner", on='user_id')

#adding a column about the duration of the passage in hours
completed['usage_duration_hours'] = completed['usage_duration'].dt.total_seconds()/3600


completed['usage_duration_hours'].describe()

count    5817.000000
mean      284.633212
std        97.625884
min         9.650278
25%       221.222500
50%       277.478889
75%       341.254444
max       741.981389
Name: usage_duration_hours, dtype: float64


fig = plt.figure(figsize=(15,7))

completed.query('type_of_completion == "victory"')['usage_duration_hours'].hist(density=True, bins=30, alpha=0.9, label="Победа над игроком")
completed.query('type_of_completion == "project"')['usage_duration_hours'].hist(density=True, bins=30, alpha=0.5, color='pink', label="Завершение проекта")

plt.title('Histogram of the time of passing the level')
plt.legend(loc="upper right")
plt.xlabel('Number of hours')
plt.ylabel('Number of observations')
plt.show()


completed.query('type_of_completion == "victory"')['usage_duration_hours'].describe()

count    3951.000000
mean      266.506688
std        98.298413
min         9.650278
25%       199.022083
50%       259.026944
75%       322.554861
max       741.981389
Name: usage_duration_hours, dtype: float64


completed.query('type_of_completion == "project"')['usage_duration_hours'].describe()

count    1866.000000
mean      323.013649
std        84.183883
min       123.374167
25%       262.832986
50%       312.761389
75%       373.789931
max       699.228611
Name: usage_duration_hours, dtype: float64


#visualize this on a graph

data_fig = completed
sns.set(rc={'figure.figsize':(10, 7)})
ax = sns.boxplot(x="type_of_completion", y="usage_duration_hours", data=data_fig, orient='v')

# add stripplot
ax = sns.stripplot(x="type_of_completion", y="usage_duration_hours", data=data_fig, color="orange", jitter=0.2, size=2.5)

# Calculate number of obs per group & median to position labels
medians = data_fig.groupby(['type_of_completion'])['usage_duration_hours'].median().values
nobs = data_fig.groupby("type_of_completion").size().values
nobs = [str(x) for x in nobs.tolist()]
nobs = ["n: " + i for i in nobs]
 
# Add it to the plot
pos = range(len(nobs))
for tick,label in zip(pos,ax.get_xticklabels()):
    plt.text(pos[tick], medians[tick] + 0.4, nobs[tick], horizontalalignment='center', size='medium', color='w', weight='semibold')

plt.xticks(rotation=45)

ax.set_ylim(-10,800)

plt.title('Time to complete the first level, depending on the method', fontsize=16)
plt.xlabel('Strategy', fontsize=14)
plt.ylabel('Transit time, hours', fontsize=14)
plt.show()


#we will save the IDs of users who have passed the first level in a separate list
completed_user_id = completed['user_id']
completed_user_id = completed_user_id.drop_duplicates().tolist()


#function for categorizing the passage of the first level by the user
def define_completion (id):
    if id in completed_user_id:
        return "completed"
    else:
        return "not_completed" 

#adding a feature   
visits['level'] = visits.apply(lambda x: define_completion(x.user_id), axis=1)


#record in a separate dataframe the events of all players who have not passed the first level
not_completed = visits.query('level == "not_completed"')

#let's add data about the time spent in the game, the first and last event to the dataframe
not_completed = pd.merge(not_completed, time, how="inner", on='user_id')

#adding a column about the duration of the passage in hours
not_completed['usage_duration_hours'] = not_completed['usage_duration'].dt.total_seconds()/3600


not_completed['usage_duration_hours'].describe()

count    67170.000000
mean       265.602491
std         99.109697
min          0.000000
25%        199.270833
50%        259.324167
75%        324.335556
max        698.023333
Name: usage_duration_hours, dtype: float64


events = len(not_completed['user_id'])
users = len(not_completed['user_id'].unique())
average_events_per_user = events / users

display('Total number of events', events)
display('Total number of users', users)
display('Average number of events per user', '{:.2f}'.format(average_events_per_user))

'Total number of events'

67170

'Total number of users'

7759

'Average number of events per user'

'8.66'


temp = not_completed.groupby('building_type')['user_id'].count().reset_index()

fig = px.bar(temp, x='building_type', y='user_id')

fig.update_layout(barmode='stack', 
                  xaxis={'categoryorder':'total descending'}, 
                  title_text='Events by building types for players who have not moved to the 2nd level',
                  yaxis=dict(
                      title='Number of events', 
                      titlefont_size=15,
                      tickfont_size=10,
                  ), width=700, height=400)
fig.show()


funnel = visits.groupby('building_type').agg({'user_id':'nunique'})\
.sort_values(by='user_id', ascending=False).reset_index(level=[0,0])

#percentage of the total number of unique users
funnel['percent'] = funnel['user_id'] / visits['user_id'].nunique()

with pd.option_context('display.float_format', '{:.2%}'.format):
    display(funnel)


with pd.option_context('display.float_format', '{:.2%}'.format):
        display(
            sources['source'].value_counts(normalize=True)
        )

yandex_direct             35.48%
instagram_new_adverts     24.65%
facebook_ads              20.08%
youtube_channel_reklama   19.78%
Name: source, dtype: float64


sns.set_theme(style="whitegrid")
sns.set(rc={'figure.figsize':(10,7)})
ax = sns.barplot(x="source", y="cost", data=costs)
plt.title('Advertising costs by channel', fontsize=16)
plt.xlabel('Source', fontsize=14)
plt.ylabel('Costs', fontsize=14)
plt.show()


costs_grouped=costs.groupby(by=["source"])['cost'].sum().reset_index()

with pd.option_context('display.float_format', '{:.0f}'.format):
        display(
            costs_grouped.sort_values(by='cost',ascending=False)
        )


costs_by_days = costs.groupby('day')['cost'].sum().reset_index()

fig = px.bar(costs_by_days, x='day', y='cost')

fig.update_layout(barmode='stack', 
                  xaxis={'categoryorder':'total descending'}, 
                  title_text='Advertising costs by day',
                  yaxis=dict(
                      title='The amount of advertising costs', 
                      titlefont_size=15,
                      tickfont_size=10,
                  ), width=700, height=400)
fig.show()


print('{:.2f}'.format(costs['cost'].sum()/players_number))

0.56


source_grouped=sources.groupby(by=["source"])['user_id'].count().reset_index()

source_grouped = pd.merge(costs_grouped, source_grouped, how="left", on='source')
source_grouped['CAC'] = source_grouped['cost'] / source_grouped['user_id']
source_grouped.sort_values("CAC", ascending=False)


time['first_ts_day'] = time['first_ts'].dt.date

temp = time.groupby('first_ts_day')['user_id'].nunique().reset_index()
temp['first_ts_day'] = temp['first_ts_day']+timedelta(days=1) #we will update the data for 1 day,
# because according to the comments of the team there is a time lag of 1 day between attracting and installing the game

costs_by_days['CAC'] = costs_by_days['cost'] / temp['user_id']
costs_by_days.sort_values(by='day')


income_per_user = 0.07
total_income = income_per_user*len(visits.query('event=="building"'))
total_costs = costs['cost'].sum()
margin = (total_income-total_costs)/total_costs

print('Revenue from displaying ads under the current monetization model', total_income)
print('Average revenue by user', '{:.2f}'.format(total_income/users))
print('Total advertising costs', '{:.2f}'.format(total_costs))
print('Maximum ROMI under the current monetization model','{:.2%}'.format(margin))

Revenue from displaying ads under the current monetization model 8956.92
Average revenue by user 1.15
Total advertising costs 7603.58
Maximum ROMI under the current monetization model 17.80%


pivot = visits.pivot_table(values='level', columns='event', index=['user_id'], aggfunc = 'count').reset_index()
pivot


#function for excluding the event about the completion of the first building from the number of events of the building type
def reduce_build(building_number):
    if building_number > 0:
        building_number_new = building_number-1
    else:
        building_number_new = building_number
    return building_number_new
         
#adding a new column to the working dataframe   
pivot['building_for_ads'] = pivot['building'].apply(reduce_build)
pivot


total_income = income_per_user * (
    pivot['building_for_ads'].sum()+pivot['finished_stage_1'].sum()+pivot['project'].sum()
)
margin = (total_income-total_costs)/total_costs

print('Revenue from displaying ads under the proposed monetization model', '{:.2f}'.format(total_income))
print('Average revenue by user', '{:.2f}'.format(total_income/users))
print('Total advertising costs', '{:.2f}'.format(total_costs))
print('Maximum ROMI under the proposed monetization model','{:.2%}'.format(margin))

Revenue from displaying ads under the proposed monetization model 8544.41
Average revenue by user 1.10
Total advertising costs 7603.58
Maximum ROMI under the proposed monetization model 12.37%


total_income = income_per_user * (
    pivot['building'].sum()+pivot['finished_stage_1'].sum()+pivot['project'].sum()
)
margin = (total_income-total_costs)/total_costs

print('Revenue from displaying ads under the proposed monetization model', '{:.2f}'.format(total_income))
print('Average revenue by user', '{:.2f}'.format(total_income/users))
print('Total advertising costs', '{:.2f}'.format(total_costs))
print('Maximum ROMI under the proposed monetization model','{:.2%}'.format(margin))

Revenue from displaying ads under the proposed monetization model 9494.73
Average revenue by user 1.22
Total advertising costs 7603.58
Maximum ROMI under the proposed monetization model 24.87%


sample_1 = completed.query('type_of_completion == "victory"')['usage_duration_hours']
sample_2 = completed.query('type_of_completion == "project"')['usage_duration_hours']

alpha = .05

results = st.ttest_ind(
    sample_1, 
    sample_2)

print('p-value:', results.pvalue)

if results.pvalue < alpha:
    print("Reject the null hypothesis")
else:
    print("Cannot reject the null hypothesis")

p-value: 7.256845329495443e-98
Reject the null hypothesis


completed['first_ts_dayweek'] = pd.DatetimeIndex(completed['first_ts']).day_name()

#divide the users who have completed the game by the days of the week

workdays = ['Monday', 'Tuesday', 'Wednesday', 'Thursday']
weekends = ['Friday', 'Saturday', 'Sunday']

sample_1 = completed.query('first_ts_dayweek in @workdays')['usage_duration_hours']
sample_2 = completed.query('first_ts_dayweek in @weekends')['usage_duration_hours']

alpha = .05

results = st.ttest_ind(
    sample_1, 
    sample_2)

print('p-value:', results.pvalue)

if results.pvalue < alpha:
    print("Reject the null hypothesis")
else:
    print("Cannot reject the null hypothesis")

p-value: 2.64064471749543e-05
Reject the null hypothesis


print('Average time to complete the game for players who came in during the working week', sample_1.mean())

Average time to complete the game for players who came in during the working week 286.1917059572618


print('Average passing time of the game for players who came at the end of the week', sample_2.mean())

Average passing time of the game for players who came at the end of the week 266.090238975117

	event_datetime	event	building_type	user_id	project_type
103924	2020-05-14 06:06:48	building	spaceport	1e9dd017-2faa-496c-b996-a6c57fb3942c	NaN
109152	2020-05-15 00:40:18	building	spaceport	d83fa13f-ce84-4e1b-b079-5ed2b6ec7897	NaN
78579	2020-05-11 00:31:23	building	spaceport	1ae5c3be-0463-4ad2-93a6-1169c58f0aee	NaN
50424	2020-05-09 00:43:26	building	assembly_shop	3546278c-c23c-4466-a901-7beabad73821	NaN
73108	2020-05-10 16:02:55	building	spaceport	5520f40c-925b-40ba-8fe6-e5c69f4da82a	NaN
121952	2020-05-17 10:30:53	building	spaceport	da3db52d-3401-47b7-b879-bbd40dac991b	NaN
31	2020-05-04 00:06:16	building	assembly_shop	5c061bf9-92bb-4ec5-b01a-e648dc10a9c1	NaN
37968	2020-05-07 23:55:39	building	spaceport	ec487be0-3318-44c2-a8b6-ea329bd077d8	NaN
71208	2020-05-10 13:04:19	building	assembly_shop	e838492c-543e-431a-807b-e9204252af88	NaN
117713	2020-05-16 12:30:39	building	research_center	198d4bdc-6cab-458d-9164-c2fcab13d4da	NaN

	user_id	source
755	0de7acab-12c9-4498-b48a-ccd746915fc7	facebook_ads
971	11a91a61-405e-49c7-8911-da5d935b388c	youtube_channel_reklama
857	0fba7003-2f85-4d75-a98a-cf4dd199e706	facebook_ads
2100	260a051d-765e-4ddb-b9fb-b04b2cbc515d	yandex_direct
12133	e4a081f1-8f41-4d29-bb02-20a5192488e8	youtube_channel_reklama
3719	448db445-6f82-49f2-87ee-6efcc7d604da	yandex_direct
4412	517c8522-8e42-4ab2-9f77-c92785de9f1a	youtube_channel_reklama
13292	facade8f-2b33-46c2-9d49-9d7c3e4aecba	youtube_channel_reklama
732	0d72e1bb-2d82-46c2-830e-8326345994cb	facebook_ads
2208	27effca9-efae-43de-910f-2c1e56c9bb7a	yandex_direct

	building_type	user_id
0	assembly_shop	55e92310-cb8e-4754-b622-597e124b03de
1	assembly_shop	c07b1c10-f477-44dc-81dc-ec82254b1347
2	assembly_shop	6edd42cc-e753-4ff6-a947-2107cd560710
3	assembly_shop	92c69003-d60a-444a-827f-8cc51bf6bf4c
4	assembly_shop	cdc6bb92-0ccb-4490-9866-ef142f09139d
...	...	...
135604	research_center	70e18c96-221c-4ccc-8b5e-45adc4b7e1a2
135615	research_center	a4f54b34-299f-495f-b29f-745465741366
135634	research_center	515c1952-99aa-4bca-a7ea-d0449eb5385a
135635	research_center	f21d179f-1c4b-437e-b9c6-ab1976907195
135637	research_center	ed3e7d02-8a96-4be7-9998-e9813ff9c316

	event_datetime	event	building_type	user_id	project_type	day_week
98034	2020-05-13 10:59:39	building	spaceport	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Wednesday
134719	2020-05-25 14:05:51	finished_stage_1	NaN	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Monday
134614	2020-05-25 07:09:53	building	research_center	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Monday
117542	2020-05-16 11:39:58	building	spaceport	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Saturday
41421	2020-05-08 06:57:30	building	assembly_shop	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Friday
115943	2020-05-16 04:29:30	building	spaceport	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Saturday
111199	2020-05-15 08:36:25	building	spaceport	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Friday
80849	2020-05-11 07:09:49	building	spaceport	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Monday
134561	2020-05-25 03:55:06	project	NaN	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	satellite_orbital_assembly	Monday
53164	2020-05-09 06:00:38	building	assembly_shop	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Saturday

	event_datetime	event	building_type	user_id	project_type	day_week
41421	2020-05-08 06:57:30	building	assembly_shop	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Friday
53164	2020-05-09 06:00:38	building	assembly_shop	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Saturday
61421	2020-05-09 20:56:03	building	assembly_shop	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Saturday
80849	2020-05-11 07:09:49	building	spaceport	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Monday
98034	2020-05-13 10:59:39	building	spaceport	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Wednesday
111199	2020-05-15 08:36:25	building	spaceport	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Friday
115943	2020-05-16 04:29:30	building	spaceport	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Saturday
117542	2020-05-16 11:39:58	building	spaceport	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Saturday
123797	2020-05-17 22:48:25	building	research_center	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Sunday
133483	2020-05-23 06:00:10	building	spaceport	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Saturday
134257	2020-05-24 12:52:53	building	research_center	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Sunday
134561	2020-05-25 03:55:06	project	NaN	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	satellite_orbital_assembly	Monday
134614	2020-05-25 07:09:53	building	research_center	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Monday
134719	2020-05-25 14:05:51	finished_stage_1	NaN	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	NaN	Monday

Table of Contents

Monetization for mobile game¶

Data upload and preparing it for analysis¶

Exploratory data analysis¶

Average number of events per user¶

Number of observations depending on time¶

The number of users in the game depending on the day of the week¶

Choosing the type of task by players¶

Distribution of observations by event type¶

Percentage of players who completed the first level¶

Adding calculated data about the time of passing the game¶

The time spent in the game by users who have passed the first level¶

Analysis of the passage time of the game depending on the method of passage¶

The time spent in the game by users who have not passed the first level¶

Number of events for a user who has not passed the first level¶

Events by building types for players who have not moved to the 2nd level¶

Marketing¶

Distribution of users by channels of attraction¶

Advertising costs by channel¶

The cost of attracting a user¶

In general, by cohort¶

By channels¶

By days¶

EDA summary¶

Monetization model¶

Testing statistical hypotheses¶

The hypothesis about the difference in the passage time of the level between users who finish the level through the implementation of the project, and users who finish the level with a victory over another player¶

Hypothesis about the difference in the time of passing the game between users who installed the game on a day off and those who came during the week¶

Conclusion and recommendations on the monetization model¶

	event_datetime	event	building_type	user_id	project_type
count	135640	135640	127957	135640	1866
unique	128790	3	3	13576	1
top	2020-05-07 03:10:43	building	spaceport	ff841992-d93b-462e-bc6e-18c24a514f36	satellite_orbital_assembly
freq	4	127957	59325	22	1866

	cost
count	28.000000
mean	271.556321
std	286.867650
min	23.314669
25%	66.747365
50%	160.056443
75%	349.034473
max	969.139394

	source	day	cost
26	youtube_channel_reklama	2020-05-08	40.217907
22	youtube_channel_reklama	2020-05-04	259.073224
10	instagram_new_adverts	2020-05-06	173.071145
1	facebook_ads	2020-05-04	548.354480
8	instagram_new_adverts	2020-05-04	502.925451
21	youtube_channel_reklama	2020-05-03	454.224943
2	facebook_ads	2020-05-05	260.185754
14	yandex_direct	2020-05-03	969.139394
13	instagram_new_adverts	2020-05-09	46.775400
17	yandex_direct	2020-05-06	180.917099

	user_id	building_count	event_datetime	event	building_type	project_type	day_week	type_of_completion
0	001d39dc-366c-4021-9604-6a3b9ff01e25	8	2020-05-12 07:40:47	finished_stage_1	NaN	NaN	Tuesday	victory
1	003ee7e8-6356-4286-a715-62f22a18d2a1	8	2020-05-17 21:47:49	finished_stage_1	NaN	NaN	Sunday	victory
2	004074f8-251a-4776-922c-8348dca1a2f6	12	2020-05-16 19:57:40	finished_stage_1	NaN	NaN	Saturday	victory
3	00464e5e-28ed-4be6-bf44-517ef7b3937b	8	2020-05-15 16:02:01	finished_stage_1	NaN	NaN	Friday	victory
4	0056c007-c082-43cc-bdef-afc49bca4cb9	3	2020-05-16 13:48:39	finished_stage_1	NaN	NaN	Saturday	victory

	building_type	user_id	percent
0	assembly_shop	13576	100.00%
1	spaceport	13231	97.46%
2	research_center	7671	56.50%

	source	cost
2	yandex_direct	2233
1	instagram_new_adverts	2161
0	facebook_ads	2141
3	youtube_channel_reklama	1068

	source	cost	user_id	CAC
0	facebook_ads	2140.904643	2726	0.785365
1	instagram_new_adverts	2161.441691	3347	0.645785
2	yandex_direct	2233.111449	4817	0.463590
3	youtube_channel_reklama	1068.119204	2686	0.397662

	day	cost	CAC
0	2020-05-03	3302.451840	0.563558
1	2020-05-04	1865.004649	0.554896
2	2020-05-05	1029.431469	0.557353
3	2020-05-06	620.476519	0.559492
4	2020-05-07	391.852033	0.569552
5	2020-05-08	242.767553	0.551744
6	2020-05-09	151.592924	0.559383

event	user_id	building	finished_stage_1	project
0	0001f83c-c6ac-4621-b7f0-8a28b283ac30	13.0	NaN	NaN
1	00151b4f-ba38-44a8-a650-d7cf130a0105	9.0	NaN	NaN
2	001aaea6-3d14-43f1-8ca8-7f48820f17aa	4.0	NaN	NaN
3	001d39dc-366c-4021-9604-6a3b9ff01e25	8.0	1.0	NaN
4	002f508f-67b6-479f-814b-b05f00d4e995	12.0	NaN	NaN
...	...	...	...	...
13571	ffef4fed-164c-40e1-bde1-3980f76d0fb5	9.0	1.0	NaN
13572	fffab3da-da0e-4e30-ae62-10d0a2e24a4e	16.0	NaN	NaN
13573	fffb626c-5ab6-47c9-8113-2062a2f18494	9.0	1.0	NaN
13574	ffff194a-56b7-4c12-860d-3485242ae7f5	7.0	NaN	NaN
13575	ffff69cc-fec1-4fd3-9f98-93be1112a6b8	12.0	1.0	1.0